  require 'fasterCSV'
  require 'pathname'

  def method_missing(method_name,arg2)
    puts method_name
  end
    
  # This method will extract the primary key from the table_name which is under connection.  
  def get_primary_key(table_name,connection)
    all_columns = connection.execute("desc #{table_name}")  
    all_columns.each{|column_detail|
      return column_detail[0] if column_detail.include?"PRI"
    }
    return false
  end   
    
    
    
  def self.method_missing(method_name,&b)
    method_array = method_name.to_s.split("_")
    $constraint_name  = method_array[0..(method_array.index("columns")-1)].join("_")
    $table_name = method_array[(method_array.index("from")+1)..(method_array.length-1)].join("_")
  end  
          
    
    
  # Generic methods which are used by all the rules validator.  
  def get_required_columns(column_array,key,value,connection)
    all_columns = []
    if value != connection
      if !key.respond_to? "join" 
        key = [key]
      end    
      key.each{|inner_value|
        if inner_value.respond_to?("abs")
          all_columns = all_columns + get_columns_from_numbers([inner_value],column_array)
        elsif inner_value.respond_to?("split")
          if get_columns_from_positions(inner_value,column_array).empty?
            all_columns = all_columns + [inner_value.to_s]    
          else    
            all_columns = all_columns + get_columns_from_positions(inner_value,column_array)
          end    
        else  
          all_columns = all_columns + [inner_value.to_s]  
        end  
      }    
      return all_columns
    end  
  end      


  # Methods for filering and finding columns
  # This metod will return names of all the columns from the given set based on the relative positions of the columns,
  def get_columns_from_positions(value,all_columns)
    column_array = []
    column_names = []
    start_from = value.split("_")[0]
    number_of_columns = value.split("_")[1].to_i
    for column in all_columns
      column_array << column
    end
    if start_from == "last"
      column_array = column_array.reverse
    end	
    for loop_counter in 0..number_of_columns-1
      if column_array[loop_counter] != nil
        column_names << column_array[loop_counter]
      end	
    end
    return column_names
  end			


  # This method will remove all the invalid chars from the column name.
  def remove_all_the_invalid_chars(column_name)
    column_name = column_name.gsub("(","_")
    column_name = column_name.gsub(")","_")
    column_name = column_name.gsub(" ","_")
    return column_name
  end


  # This will return column names of the table_name which is under connection.
  def get_column_list(connection,table_name)
    column_array = []  
    all_columns = connection.execute("desc #{table_name};")
    all_columns.each{|val|
      column_array << val[0]
    }
    return column_array
  end  

    
  def compare_csv_files(filepath_one,filepath_two)
    match_found = 0
    FasterCSV.foreach("#{filepath_one}") do |row_one_value|
      FasterCSV.foreach("#{filepath_two}") do |row_two_value|
        return true if row_one_value == row_two_value
      end
    end
  end


  # This method will return names of all the columns from the given set based on the numeric array provided.
  def get_columns_from_numbers(number_array,all_columns)
    column_counter = 1 
    column_names = []
    for column in all_columns 
      if number_array.include?column_counter
        column_names << column	
      end	
      column_counter = column_counter + 1
    end
    return column_names
  end		
  

  # This method will return names of the columns from a given table which are following a given constraint
  def get_columns_by_constraint(constraint,table_name,connection)
    constraint_name = get_constraint_term(constraint) 
    column_names = []
    all_columns = connection.execute("desc #{table_name}")
    for column in all_columns
      if column.include?(constraint_name)
        column_names = column_names + [column[0]]
      end  
    end
    return column_names   
  end  


  # Column interaction methods:
  # This method will return the datatype and size of a column_name belonging to given table_name
  def get_the_col_spec_for_single_col(column_name,table_name,new_connection)
    col_struc = new_connection.execute("desc #{table_name}")
    col_struc.each do |col|
      column_one = first_letter_capital(col[0])
      column_two = first_letter_capital(column_name)
      if col[0] == column_name || col[0].upcase == column_name || col[0].downcase == column_name || column_one == column_two
        return col[1]
      end	
    end	
  end
  

  # This method will generate a profile of a single column.
  def generate_column_profile(column,table_name,connection)
    column_name = ""
    column_specs = ""
    if !column.respond_to? "join"
      column_name = column
      column_specs = get_the_col_spec_for_single_col(column,table_name,connection)
    else
      column_name = column[0]
      column_specs = column[1]
    end  
    all_values = []
    sum = 0
    square_sum = 0
    connection.select_all("select #{column_name} from #{table_name}").each{|value|
      if value[0].nil? or value.empty?
        value = [" "]
      end
      all_values << value.first.to_s.length
    }
    all_values = all_values.sort
    print_column_profiling_result(column_name,column_specs,all_values)
  end    
    

  # This method will print the column profile results obtained from the earlier method.      
  def print_column_profiling_result(column_name,column_specs,all_values)
    puts "Maximum value of #{column_name} is #{all_values.last} in size"
    puts "Maximum value of #{column_name} is #{all_values.first} in size"
    puts "Column name is #{column_name}"
    puts "Column datatype is #{column_specs.split("(")[0]}" 
    has_size = column_specs.split("(")[1]
    if has_size
      size = has_size.split(")")[0]
      puts "Column size is #{size}" 
      all_value = all_values.last.to_i
      size = size.to_i
      if size > all_value
        puts "Maximum size of the column is #{size - all_value} less than the specified size of the #{column_name}."
        puts "#{column_name} is not fully using resources."
        puts "#{(((size - all_value).to_f/size)*100).round}% resources are wasted by #{column_name}"
      else
        puts "Maximum size of the column is matching the column's specified size"
      end    
    end
    puts 
    puts
  end


  # If column_name does not exist in the table_name then thsi method will add it.
  def add_column_if_does_not_exists(column_name,table_name,connection)
    all_columns = connection.execute("desc #{table_name}") 
    for column in all_columns
        return if column[0].to_s == column_name.to_s
    end
    connection.execute("ALTER TABLE #{table_name} ADD #{column_name} VARCHAR(255)")
  end


  # This method will return column structure for all the columns in a table based on the structure file,
  def get_the_col_specs(column_name,table_name)
    filename="#{table_name}_structure.csv"
    FasterCSV.foreach(filename) do |row|
      for field_counter in 0..row.length
        return row[field_counter+1] if row[field_counter] == column_name
      end
    end
  end

  
  # Primary key related methods.
  #This method is handling some primary key related scenarios. These are as follows:
  #1.  In the first_table the primary key is already there and is diff from the one mentioned by user.
  #2.  In the  first_table the primary key is already there and is same as the one mentioned by user. 
  #3.  In the first_table there is no primary key.
  #4.  In the first_table the column mentioned by user is already there but it is not the primary key.
  def handle_primary_key(first_table_primary_key,first_table,new_connection)
    result_array = primary_key_column_exists(first_table_primary_key,first_table,new_connection)
    if result_array[0] == 1
      first_row_specs = get_the_col_spec_for_single_col(result_array[1],first_table,new_connection)
      puts "The primary key you have mentioned is already existing. So that has been taken."
    elsif result_array[0] == 2
      puts "#{first_table} already has primary key other than the one you have mentioned."
      first_row_specs = 0
    elsif result_array[0] == 3
      new_connection.execute("ALTER TABLE #{first_table} ADD PRIMARY KEY (#{result_array[1]})")
      first_row_specs = get_the_col_spec_for_single_col(first_table_primary_key,first_table,new_connection)
    else 
      new_connection.execute("ALTER TABLE #{first_table} ADD #{first_table_primary_key} int(11) PRIMARY KEY AUTO_INCREMENT")
      #~ new_connection.execute("ALTER TABLE #{first_table} ADD PRIMARY KEY (#{first_table_primary_key})")
      first_row_specs = "int(11)"
      puts "For #{first_table} non-existant column has been made as a primary key."
      puts "Application has generated new column with same name"
    end		
    return first_row_specs
  end 


  # Given the table name and the primary key title, this method determines which column can  be a primary key based on simple heuristics. 
  def get_possible_primary_key(primary_key,table_name,connection)
    all_columns = connection.execute("desc #{table_name}") 
    for column in all_columns
      if column[0] == primary_key || column[0] == first_letter_capital(primary_key) || column[0] == primary_key.capitalize || column[0] == primary_key.gsub(/\_/,"") 
        return column[0]
      end	
    end
  end
          
          
  #This method verifies if the primary key mentioned in the config file is actually there in the table. 
  #This method ensures that application generates id as a default primary key in case of a missing primary key. 
  def primary_key_column_exists(primary_key,table,new_connection)
    table_struc = new_connection.execute("desc #{table}") 
    for field in table_struc
      if field.include?"PRIMARY KEY" or field.include?"primary key" or field.include?"PRI"
        if field[0] == primary_key || field[0] == first_letter_capital(primary_key) || field[0] == primary_key.capitalize || field[0] == primary_key.gsub(/\_/,"") 
          return [1,field[0]]
        else	
          return [2,"no_primary_key"]
        end		
      else
        if field[0] == primary_key || field[0] == first_letter_capital(primary_key) || field[0] == primary_key.capitalize || field[0] == primary_key.gsub(/\_/,"") 
          return [3,field[0]]
        end	  
      end	
    end
    return [4,"no_primary_key"]
  end	  
    

  # Methods for handling simple heuristics for the constraint names.
  def get_constraint_term(constraint)
    case constraint.downcase 
      when "not_null"
        return "NO"
      when "primary_key"
        return "PRI"
      when "auto_increment"
        return "auto_increment"
      when "unique"
        return "unique"    
      end
  end  
      
      
  # This method will generate possible third table names.   
  # This is used during the relationship transforms.
  def generate_possible_third_table_name(first_table,second_table,connection)
    first_option = first_table.to_s+"_"+second_table.to_s
    all_tables = connection.tables
    for table in all_tables
      first_option = first_option+"new" if table==first_option
    end
    return first_option  
  end


  # This method converts the primary key name into camel case for comparison.
  def first_letter_capital(field_name)
    if !field_name.nil?
      field_names = field_name.split("_")
      for str in field_names
        str.capitalize!
      end	
      field_name = field_names.join("")
      return field_name
    end    
  end


  # File generating methods.
  # Header file generation:if the table where data is to be loaded already exists then its headers are extracted so that insert data into correct columns. 
  def generate_header_file(tablename,connection,column_list)
    all_columns = []
    if column_list
      all_columns = column_list
    else  
      all_columns = get_column_list(connection,table_name)
    end    
    header_file_handler = File.new("#{tablename}_header.csv","w")
    all_columns.each do |column_name|
      header_file_handler.write(column_name+",")
    end		
    header_file_handler.close
    return "#{tablename}_header.csv"
  end	  


  #This file create the global_connection.csv 
  #This file is refernced everytime an ETL task is run. 
  #Any changes in the file will be picked up from the next task run.
  def create_global_connection_file
    config_file = File.new("global_connection.csv","w")
    config_file.write("database_type,")
    config_file.write("\n")
    config_file.write("database,")
    config_file.write("\n")
    config_file.write("host,")
    config_file.write("\n")
    config_file.write("password,")
    config_file.write("\n")
    config_file.write("username,")
    config_file.close
  end
  
  #This file creates the structure file of the root table. 
  #Useful in all the transformations.
  def create_structure_csv_file(table_name,new_connection)
    config_file_handler = File.new("#{table_name}_structure.csv",'w') 
    table_structure = new_connection.execute("desc #{table_name}")
    table_structure.each do |table_info|
      config_file_handler.write(table_info[0])
      config_file_handler.write(",")
      config_file_handler.write(table_info[1])
      config_file_handler.write("\n")
    end
    config_file_handler.close	
  end


  # Connection establishers for the RAKE TASKS.
  #Establishes a new connection based on the options given in the cofig file. 
  #This method also checks the configuration options and throws an error if none can be found.
  def create_new_connection
    connection_option = extract_connection_options
    if connection_option[0] == nil || connection_option[1] == nil || connection_option[2] == nil
      puts "The database connection could not be established. This might be due to one of the following options being null:
      1. Database name
      2. Database type
      3. Hostname"
    else
      database_type = connection_option[0]
      database = connection_option[1]
      host = connection_option[2]
      password = connection_option[3]
      username = connection_option[4]
      if connection_option[3].nil? || connection_option[3].empty?
        new_connection = DBI.connect("DBI:#{connection_option[0].capitalize}:#{connection_option[1]}:#{connection_option[2]}", "#{connection_option[4]}", "")
      else
        new_connection = DBI.connect("DBI:#{connection_option[0].capitalize}:#{connection_option[1]}:#{connection_option[2]}", "#{connection_option[4]}", "#{connection_option[3]}")
      end	
      return new_connection
    end	
  end

  
  #This method is extractin connection options from the global_connection.csv
  def extract_connection_options
    connection_options = [] 
    FasterCSV.foreach("global_connection.csv") do |row|
      for field_counter in 0..row.length
        case row[field_counter]
          when "database_type"
            connection_options[0] = row[field_counter+1]
          when "database"
            connection_options[1] = row[field_counter+1]
          when "host"
            connection_options[2] = row[field_counter+1]
          when "password"
            connection_options[3] = row[field_counter+1]
          when "username"
            connection_options[4] = row[field_counter+1]
        end		
      end	
    end
    return connection_options	
  end	 
  

  #This method extracts names of all the root tables.
  #Important while doing the normalization transforms.
  def get_root_tables(filename) 
    root_table_array = []  
    FasterCSV.foreach("#{filename}") do |row|
      if row.first == "root table" 
        for field_counter in 1..row.length-1
          root_table_array << row[field_counter]
        end
      end
    end	
    return root_table_array
  end

 

 
 	 
 	 

 